package icu.twtool.database.mapper

import icu.twtool.constant.toDateTime
import icu.twtool.database.DatabaseUtil
import icu.twtool.database.model.PracticeRecord
import icu.twtool.database.vo.PracticeExerciseRecordVO

/**
 * 练习记录数据库工具
 *
 * @author Wen
 * @since 2022-11-05
 */
object PracticeRecordMapper {

    private const val ID: String = "ID"
    private const val EXERCISE_ID: String = "EXERCISE_ID"
    private const val RIGHT_COUNT: String = "RIGHT_COUNT"
    private const val PRACTICE_TIME: String = "PRACTICE_TIME"
    private const val PRACTICE_FILE: String = "PRACTICE_FILE"

    fun insert(exerciseId: Int, rightCount: Int, practiceFile: String) {
        DatabaseUtil.connection {
            prepareStatement("insert into TB_PRACTICE_RECORD(EXERCISE_ID, RIGHT_COUNT, PRACTICE_FILE) values (?, ?, ?)").apply {
                setInt(1, exerciseId)
                setInt(2, rightCount)
                setString(3, practiceFile)
                execute()
            }
        }
    }

    fun selectPage(offset: Int, size: Int): List<PracticeExerciseRecordVO> {
        return DatabaseUtil.connection {
            val result = mutableListOf<PracticeExerciseRecordVO>()
            val resultSet =
                prepareStatement("select P.ID, EXERCISE_ID, RIGHT_COUNT, PRACTICE_TIME, PRACTICE_FILE, NAME, COUNT, REALLY_NAME from TB_PRACTICE_RECORD as P, TB_EXERCISE_METE_DATA as E where P.EXERCISE_ID = E.ID order by PRACTICE_TIME desc limit ? offset ?")
                    .apply {
                        setInt(1, size)
                        setInt(2, offset)
                    }
                    .executeQuery()
            while (resultSet.next()) {
                result.add(
                    PracticeExerciseRecordVO(
                        PracticeRecord(
                            resultSet.getInt(ID),
                            resultSet.getInt(EXERCISE_ID),
                            resultSet.getInt(RIGHT_COUNT),
                            resultSet.getString(PRACTICE_TIME).toDateTime(),
                            resultSet.getString(PRACTICE_FILE),
                        ),

                        resultSet.getString(ExerciseMeteDataMapper.NAME),
                        resultSet.getInt(ExerciseMeteDataMapper.COUNT),
                        resultSet.getString(ExerciseMeteDataMapper.REALLY_NAME)
                    )
                )
            }
            result
        } ?: listOf()
    }

    fun count(): Int {
        return DatabaseUtil.connection {
            val resultSet = prepareCall("select count(ID) from TB_PRACTICE_RECORD").executeQuery()
            if (resultSet.next()) {
                resultSet.getInt(1)
            } else 0
        } ?: 0
    }

    fun countByExerciseId(id: Int): Int {
        return DatabaseUtil.connection {
            prepareStatement("select count(ID) from TB_PRACTICE_RECORD where EXERCISE_ID = ?").run {
                setInt(1, id)
                val resultSet = executeQuery()
                if (resultSet.next()) {
                    resultSet.getInt(1)
                } else 0
            }
        } ?: 0
    }
}